﻿
#region NameSpace

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using EB.Generics;

#endregion

namespace ProductManagement
{

#region  Struct

    public struct Materials
    {
        string _materialID;
        public string MaterialID
        {
            get { return _materialID; }
            set { _materialID = value; }
        }

        string _materialName;
        public string MaterialName
        {
            get { return _materialName; }
            set { _materialName = value; }
        }

        string _catgoryID;
        public string CategoryID
        {
            get { return _catgoryID; }
            set { _catgoryID = value; }
        }

        string _vendorID;
        public string VendorID
        {
            get { return _vendorID; }
            set { _vendorID = value; }
        }

        string _price;
        public string Price
        {
            get { return _price; }
            set { _price = value; }
        }
    }

    public struct PurchaseRequest
    {
        string _materialID;
        public string MaterialID
        {
            get {  return _materialID; }
            set { _materialID = value; }
        }

        string _requestQuantity;
        public string RequestQuantity
        {
            get { return _requestQuantity; }
            set { _requestQuantity = value; }
        }

        string _requestDate;
        public string RequestDate
        {
            get { return _requestDate; }
            set { _requestDate = value; }
        }

        string _priority;
        public string Priority
        {
            get { return _priority; }
            set { _priority = value; }
        }

        string _requesterID;
        public string RequesterID
        {
            get { return _requesterID; }
            set { _requesterID = value; }
        }

        string _requesterRemarks;
        public string RequesterRemarks
        {
            get { return _requesterRemarks; }
            set { _requesterRemarks = value; }
        }
    }

    public struct Vendor
    {
        string _vendorID;
        public string VendorID
        {
            get { return _vendorID; }
            set { _vendorID = value; }
        }

        string _vendorName;
        public string VendorName
        {
            get { return _vendorName; }
            set { _vendorName = value; }
        }

        string _address;
        public string Address
        {
            get { return _address; }
            set { _address = value; }
        }

        string _place;
        public string Place
        {
            get { return _place; }
            set { _place = value; }
        }

        string _state;
        public string State
        {
            get { return _state; }
            set { _state = value; }
        }

        string _pin;
        public string Pin
        {
            get { return _pin; }
            set { _pin = value; }
        }

        string _phone;
        public string Phone
        {
            get { return _phone; }
            set { _phone = value; }
        }
        

        string _email;
        public string Email
        {
            get { return _email; }
            set { _email = value; }
        }

        string _primaryContactPerson;
        public string PrimaryContactPerson
        {
            get { return _primaryContactPerson; }
            set { _primaryContactPerson = value; }
        }


        string _primaryContactMobile;
        public string PrimaryContactMobile
        {
            get { return _primaryContactMobile; }
            set { _primaryContactMobile = value; }
        }



    }

    public struct Product
    {
        string _ID;
        public string ID
        {
            get { return _ID; }
            set { _ID = value; }
        }

        string _Name;
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }

        string _description;
        public string Description
        {
            get { return _description; }
            set { _description = value; }
        }

        string _productCode;
        public string ProductCode
        {
            get { return _productCode; }
            set { _productCode = value; }
        }

        string _upc;
        public string UPC
        {
            get { return _upc; }
            set { _upc = value; }
        }

        string _listPrice;
        public string ListPrice
        {
            get { return _listPrice; }
            set { _listPrice = value; }
        }

        string _type;
        public string Type
        {
            get { return _type; }
            set { _type = value; }
        }

        string _stock;
        public string Stock
        {
            get { return _stock; }
            set { _stock = value; }
        }


        string _unit;
        public string Unit
        {
            get { return _unit; }
            set { _unit = value; }
        }


        string _specification;
        public string Specification
        {
            get { return _specification; }
            set { _specification = value; }
        }
    }

    public struct Accessory
    {
        string _id;
        public string ID
        {
            get { return _id; }
            set { _id = value; }
        }

        string _Name;
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }

        string _listPrice;
        public string ListPrice
        {
            get { return _listPrice; }
            set { _listPrice = value; }
        }

        string _upc;
        public string UPC
        {
            get { return _upc; }
            set { _upc = value; }
        }

        string _description;
        public string Description
        {
            get { return _description; }
            set { _description = value; }
        }

        string _specification;
        public string Specification
        {
            get { return _specification; }
            set { _specification = value; }
        }

    }

#endregion

    /// <summary>
    /// Summary description for Purchase
    /// </summary>
    public class Products
    {

        Generics _generics = new Generics();
        SqlConnection connection;
        string _connectionString = string.Empty;

        #region SqlConnection
        //private static string connectionString = ConfigurationManager.ConnectionStrings["connectUserManager"].ToString();
        //private static SqlConnection _sqlcon = new SqlConnection(connectionString);
        //public static SqlConnection Sqlcon
        //{
        //    get { return _sqlcon; }
        //    set { _sqlcon = value; }
        //}

        //SqlCommand _sqlcom = new SqlCommand("", Sqlcon);
        //public SqlCommand Sqlcom
        //{
        //    get
        //    {
        //        return _sqlcom;
        //    }
        //    set
        //    {
        //        _sqlcom = value;
        //    }
        //}

        //static SqlDataAdapter _sqlada = new SqlDataAdapter("", Sqlcon);
        //public static SqlDataAdapter SqlAda
        //{
        //    get
        //    {
        //        return _sqlada;
        //    }
        //    set
        //    {
        //        _sqlada = value;
        //    }
        //}

        //SqlCommandBuilder _sqlcomb = new SqlCommandBuilder(SqlAda);
        //public SqlCommandBuilder Sqlcomb
        //{
        //    get
        //    {
        //        return _sqlcomb;
        //    }
        //    set
        //    {
        //        _sqlcomb = value;
        //    }
        //}



        DataSet _ds = new DataSet();
        public DataSet Ds
        {
            get
            {
                return _ds;
            }
            set
            {
                _ds = value;
            }
        }

        DataRow _dr;
        public DataRow Dr
        {
            get
            {
                return _dr;
            }
            set
            {
                _dr = value;
            }
        }
        #endregion


        public Products()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["connectUserManager"].ToString();
            //"Data Source=198.64.249.24,1092;Initial Catalog=eInternShip;User Id=bmjobmjo;Password=goodhopealways";
            connection = new SqlConnection(_connectionString);
        }

        #region General Fns

        public DataSet GetData(string table,string condition)
        {
            Ds.Clear();
            //if (condition == string.Empty)
            //    SqlAda.SelectCommand.CommandText = "select * from " + table + " ";
            //else
            //    SqlAda.SelectCommand.CommandText = "select * from " + table + " where " + condition + " ";
            //SqlAda.Fill(Ds);
            string query;
            if (condition == string.Empty)
                query = "select * from " + table + " ";
            else
                query = "select * from " + table + " where " + condition + " ";
            Ds=_generics.ExecuteQuery(query);
            return Ds;
        }

        public DataSet GetData(string table1, string table2, string condition)
        {
            Ds.Clear();
            //SqlAda.SelectCommand.CommandText = "select T1.*,T2.* from " + table1 + " as T1," + table2 + " as T2 where " + condition + " ";            
            //Ds = new DataSet();
            //SqlAda.Fill(Ds);
            string query = "select T1.*,T2.* from " + table1 + " as T1," + table2 + " as T2 where " + condition + " ";
            Ds=_generics.ExecuteQuery(query);
            return Ds;
        }

#endregion

    
        #region  Product

        public bool SearchProduct(string criteria,string toSearch, out DataSet result)
        {
            result = new DataSet();
            
            //SqlAda.SelectCommand.CommandText = "select * from ProductList where ProductName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
            //if(criteria == "UPC")
            //    SqlAda.SelectCommand.CommandText = "select * from ProductList where UPC like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
            //else if (criteria == "Description")
            //    SqlAda.SelectCommand.CommandText = "select * from ProductList where ProductDescription like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
            //SqlAda.Fill(result);
            //return true;

            string query = "select * from ProductsProductType where ProductName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
            if (criteria == "UPC")
                query = "select * from ProductsProductType where UPC like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
            else if (criteria == "Description")
                query = "select * from ProductsProductType where ProductDescription like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
            result=_generics.ExecuteQuery(query);
            return true;

        }

        public bool AddProduct(Product addProduct)
        {
            //SqlAda.SelectCommand.CommandText = "select * from ProductList where ProductName = '" + addProduct.Name + "'";
            //SqlAda.Fill(Ds);

            SqlDataAdapter sqlAda = new SqlDataAdapter("", _connectionString);
            sqlAda.SelectCommand.CommandText = "select * from ProductList where ProductName = '" + addProduct.Name + "'";
            SqlCommandBuilder sqlComBld = new SqlCommandBuilder(sqlAda);
            sqlAda.Fill(Ds);

            if (Ds.Tables[0].Rows.Count != 0)
                return false;

            Dr = Ds.Tables[0].NewRow();
            Dr["ProductName"] = addProduct.Name;
            Dr["ProductDescription"] = addProduct.Description;
            Dr["ProductCode"] = addProduct.ProductCode;
            Dr["UPC"] = addProduct.UPC;
            if (addProduct.ListPrice != string.Empty)
                Dr["ListPrice"] = addProduct.ListPrice;
            Dr["Type"] = addProduct.Type;
            if (addProduct.Stock != string.Empty)
                Dr["Stock"] = addProduct.Stock;
            if (addProduct.Unit != string.Empty)
                Dr["Unit"] = addProduct.Unit;
            Dr["Specification"] = addProduct.Specification;

            Ds.Tables[0].Rows.Add(Dr);
            sqlAda.Update(Ds);
            return true;
        }

        public bool EditProduct(Product editProduct)
        {
            //Sqlcom.CommandText = "update ProductList set ProductCode = '" + editProduct.ProductCode + "' , ProductName = '" + editProduct.Name + "' ," +
            //                     " ProductDescription = '" + editProduct.Description + "' ,UPC = '" + editProduct.UPC + "' ," + 
            //                     "ListPrice = '" + editProduct.ListPrice + "' ,Type = '" + editProduct.Type + "',Stock = '" + editProduct.Stock + "'," +
            //                     "Unit = '" + editProduct.Unit + "' ,Specification = '" + editProduct.Specification + "' where ProductID = '" + editProduct.ID + "'";

            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();

            string query = "update ProductList set ProductCode = '" + editProduct.ProductCode + "' , ProductName = '" + editProduct.Name + "' ," +
                                 " ProductDescription = '" + editProduct.Description + "' ,UPC = '" + editProduct.UPC + "' ," +
                                 "ListPrice = '" + editProduct.ListPrice + "' ,Type = '" + editProduct.Type + "',Stock = '" + editProduct.Stock + "'," +
                                 "Unit = '" + editProduct.Unit + "' ,Specification = '" + editProduct.Specification + "' where ProductID = '" + editProduct.ID + "'";
            _generics.ExecuteNonQuery(query);
            return true;
        }

        public bool DeleteProduct(string conditionDeleteProduct, string conditionDeleteProductSupplierList, string conditionDeleteProductAccessory)
        {
            //Sqlcom.CommandText = "delete from ProductSupplierList where " + conditionDeleteProductSupplierList + "";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();

            //Sqlcom.CommandText = "delete from ProductAccessoriesList where " + conditionDeleteProductAccessory + "";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();
           
            //Sqlcom.CommandText = "delete from ProductList where " + conditionDeleteProduct + "";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();


            string query = "delete from ProductSupplierList where " + conditionDeleteProductSupplierList + "";
            _generics.ExecuteNonQuery(query);

            query = "delete from ProductAccessoriesList where " + conditionDeleteProductAccessory + "";
            _generics.ExecuteNonQuery(query);

            query = "delete from ProductList where " + conditionDeleteProduct + "";
            _generics.ExecuteNonQuery(query);

            return true;
        }

        public string GetMaxProductID()
        {
            //SqlAda.SelectCommand.CommandText = "select max(ProductID) as ID from ProductList ";
            //SqlAda.Fill(Ds);
            string query = "select max(ProductID) as ID from ProductList ";
            Ds=_generics.ExecuteQuery(query);
            string ID = Ds.Tables[0].Rows[0]["ID"].ToString();
            return ID;
        }

        #endregion


        #region  Accessories

        public bool SearchAccessory(string productID,string toSearch, out DataSet result)
        {
            result = new DataSet();
            //if (productID != string.Empty)
            //    SqlAda.SelectCommand.CommandText = "Select * from ProductAccessories where ProductID = '" + productID + "'";
            //else
            //    SqlAda.SelectCommand.CommandText = "select * from AccessoriesList where AccessoryName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
           
            //SqlAda.Fill(result);

            string query;
            if (productID != string.Empty)
                query = "Select * from ProductAccessories where ProductID = '" + productID + "'";
            else
                query = "select * from AccessoriesList where AccessoryName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
            result = _generics.ExecuteQuery(query);
            return true;

        }

        public bool AddAccessory(Accessory addAccessory)
        {
            SqlDataAdapter sqlAda = new SqlDataAdapter("",_connectionString);
            sqlAda.SelectCommand.CommandText = "select * from AccessoriesList where AccessoryName = '" + addAccessory.Name + "'";
            SqlCommandBuilder sqlComBld = new SqlCommandBuilder(sqlAda);
            sqlAda.Fill(Ds);

            if (Ds.Tables[0].Rows.Count != 0)
                return false;

            Dr = Ds.Tables[0].NewRow();
            Dr["AccessoryName"] = addAccessory.Name;
            if (addAccessory.ListPrice != string.Empty)
                Dr["ListPrice"] = addAccessory.ListPrice;
            Dr["UPC"] = addAccessory.UPC;
            Dr["Description"] = addAccessory.Description;
            Dr["Specification"] = addAccessory.Specification;            

            Ds.Tables[0].Rows.Add(Dr);
            sqlAda.Update(Ds);
            return true;
        }

        public bool EditAccessory(Accessory editAccessory)
        {
            //Sqlcom.CommandText = "update AccessoriesList set  AccessoryName = '" + editAccessory.Name + "' ,Description = '" + editAccessory.Description + "' ," +
            //    "UPC = '" + editAccessory.UPC + "' ,ListPrice = '" + editAccessory.ListPrice + "' ,Specification = '" + editAccessory.Specification + "' " + 
            //    "where AccessoryID = '" + editAccessory.ID + "'";

            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();

            string query ="update AccessoriesList set  AccessoryName = '" + editAccessory.Name + "' ,Description = '" + editAccessory.Description + "' ," +
                "UPC = '" + editAccessory.UPC + "' ,ListPrice = '" + editAccessory.ListPrice + "' ,Specification = '" + editAccessory.Specification + "' " + 
                "where AccessoryID = '" + editAccessory.ID + "'";
            _generics.ExecuteNonQuery(query);
            return true;
        }

        public bool DeleteAccessory(string conditionDeleteAccessory, string conditionDeleteProductAccessory)
        {
            //Sqlcom.CommandText = "delete from ProductAccessoriesList where " + conditionDeleteProductAccessory + "";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();

            //Sqlcom.CommandText = "delete from AccessoriesList where " + conditionDeleteAccessory + "";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();

            string query = "delete from ProductAccessoriesList where " + conditionDeleteProductAccessory + "";
            _generics.ExecuteNonQuery(query);

            query = "delete from AccessoriesList where " + conditionDeleteAccessory + "";
            _generics.ExecuteNonQuery(query);

            return true;
        }

        public bool AddProductAccessory(string productID,string accessoryID)
        {
            //if ((accessoryID == "&nbsp;") || (accessoryID == string.Empty ))
            //    accessoryID = "0";
            //SqlAda.SelectCommand.CommandText = "select * from ProductAccessoriesList where ProductID = " + productID + " and " +
            //    "AccessoryID = " + accessoryID + " ";
            //Ds.Clear();
            //SqlAda.Fill(Ds);

            SqlDataAdapter sqlAda = new SqlDataAdapter("", _connectionString);
            //if ((accessoryID == "&nbsp;") || (accessoryID == string.Empty))
            //    accessoryID = "0";
            sqlAda.SelectCommand.CommandText = "select * from ProductAccessoriesList where ProductID = " + productID + " and " +
                "AccessoryID = " + accessoryID + " ";            
            SqlCommandBuilder sqlComBld = new SqlCommandBuilder(sqlAda);
            sqlAda.Fill(Ds);

            if (Ds.Tables[0].Rows.Count != 0)
                return false;
           

            Dr = Ds.Tables[0].NewRow();
            Dr["ProductID"] = productID;
            Dr["AccessoryID"] = accessoryID;
            Ds.Tables[0].Rows.Add(Dr);
            sqlAda.Update(Ds);
            return true;
        }

       

        public bool DeleteProductAccessory(string productID, string accessoryID)
        {
            //Sqlcom.CommandText = "delete from ProductAccessoriesList where ProductID = '" + productID + "' and AccessoryID = '" + accessoryID + "'";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();

            string query = "delete from ProductAccessoriesList where ProductID = '" + productID + "' and AccessoryID = '" + accessoryID + "'";
            _generics.ExecuteNonQuery(query);

            return true;
        }
        
        #endregion

         
        #region Vendor
        public bool SearchVendor(string productID, string toSearch, out DataSet result)
        {
            result = new DataSet();
            //if (productID != string.Empty)
            //    SqlAda.SelectCommand.CommandText = "Select * from ProductSupplier where ProductID = '" + productID + "'";
            //else
            //    SqlAda.SelectCommand.CommandText = "select * from VendorList where VendorName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
            //SqlAda.Fill(result);

            string query;
            if (productID != string.Empty)
                query = "Select * from ProductSupplier where ProductID = '" + productID + "'";
            else
                query = "select * from VendorList where VendorName like '" + '%' + "' + '" + toSearch + "' + '" + '%' + "'";
            result = _generics.ExecuteQuery(query);
            return true;

        }

 

        public bool AddVendor(Vendor vendor)
        {
            //SqlAda.SelectCommand.CommandText = "select * from VendorList where VendorName = '" + vendor.VendorName + "'";
            //SqlAda.Fill(Ds);

            SqlDataAdapter sqlAda = new SqlDataAdapter("", _connectionString);
            sqlAda.SelectCommand.CommandText = "select * from VendorList where VendorName = '" + vendor.VendorName + "'";
            SqlCommandBuilder sqlComBld = new SqlCommandBuilder(sqlAda);
            sqlAda.Fill(Ds);

            if (Ds.Tables[0].Rows.Count != 0)
                return false;

            Dr = Ds.Tables[0].NewRow();
            Dr["VendorName"] = vendor.VendorName;
            Dr["Address"] = vendor.Address;
            Dr["Place"] = vendor.Place;
            Dr["State"] = vendor.State;
            Dr["Pin"] = vendor.Pin;
            Dr["Phone"] = vendor.Phone;            
            Dr["Email"] = vendor.Email;
            Dr["PrimaryContactPerson"] = vendor.PrimaryContactPerson;
            Dr["PrimaryContactMobile"] = vendor.PrimaryContactMobile;

            Ds.Tables[0].Rows.Add(Dr);
            sqlAda.Update(Ds);
            return true;

        }

        public bool EditVendor(Vendor vendor)
        {
            //Sqlcom.CommandText = "update VendorList set VendorName = '" + vendor.VendorName + "',Address = '" + vendor.Address + "', " +
            //"Place = '" + vendor.Place + "',State = '" + vendor.State + "',Pin = '" + vendor.Pin + "',Phone = '" + vendor.Phone + "', " +
            //"Email = '" + vendor.Email + "',PrimaryContactPerson = '" + vendor.PrimaryContactPerson + "',PrimaryContactMobile = '" + vendor.PrimaryContactMobile + "' " +
            //"where VendorID = '" + vendor.VendorID + "'";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();

            string query = "update VendorList set VendorName = '" + vendor.VendorName + "',Address = '" + vendor.Address + "', " +
            "Place = '" + vendor.Place + "',State = '" + vendor.State + "',Pin = '" + vendor.Pin + "',Phone = '" + vendor.Phone + "', " +
            "Email = '" + vendor.Email + "',PrimaryContactPerson = '" + vendor.PrimaryContactPerson + "',PrimaryContactMobile = '" + vendor.PrimaryContactMobile + "' " +
            "where VendorID = '" + vendor.VendorID + "'";
            _generics.ExecuteNonQuery(query);
            return true;
        }

        public bool DeleteVendor(string conditionDeleteProductSupplier, string conditionDeleteVendor)
        {
            //_sqlcom = new SqlCommand("", _sqlcon);       

            //Sqlcom.CommandText = "delete from ProductSupplierList where " + conditionDeleteProductSupplier + "";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();

            //Sqlcom.CommandText = "delete from VendorList where " + conditionDeleteVendor + "";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();           


            string query = "delete from ProductSupplierList where " + conditionDeleteProductSupplier + "";
            _generics.ExecuteNonQuery(query);

            query = "delete from VendorList where " + conditionDeleteVendor + "";
            _generics.ExecuteNonQuery(query);
            return true;
        }

        public bool AddProductSupplier(string productID, string supplierID, string priority)
        {
            //if ((supplierID == "&nbsp;") || (supplierID == string.Empty))
            //    supplierID = "0";
            //SqlAda.SelectCommand.CommandText = "select * from ProductSupplierList where ProductID = " + productID + " and " +
            //    "VendorID = " + supplierID + " ";
            //Ds.Clear();
            //SqlAda.Fill(Ds);

            SqlDataAdapter sqlAda = new SqlDataAdapter("", _connectionString);
            sqlAda.SelectCommand.CommandText = "select * from ProductSupplierList where ProductID = " + productID + " and " + "VendorID = " + supplierID + " ";
            SqlCommandBuilder sqlComBld = new SqlCommandBuilder(sqlAda);
            sqlAda.Fill(Ds);
            if (Ds.Tables[0].Rows.Count != 0)
                return false;


            Dr = Ds.Tables[0].NewRow();
            Dr["ProductID"] = productID;
            Dr["VendorID"] = supplierID;
            Dr["Preference"] = priority;
            Ds.Tables[0].Rows.Add(Dr);
            sqlAda.Update(Ds);
            return true;
        }


        public bool DeleteProductSupplier(string productID, string supplierID)
        {
            //Sqlcom.CommandText = "delete from ProductSupplierList where ProductID = '" + productID + "' and VendorID = '" + supplierID + "'";
            //Sqlcon.Open();
            //Sqlcom.ExecuteNonQuery();
            //Sqlcon.Close();

            string query = "delete from ProductSupplierList where ProductID = '" + productID + "' and VendorID = '" + supplierID + "'";
            _generics.ExecuteNonQuery(query);
            return true;
        }


        #endregion


      
    }
}

